﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Web;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace Web
{
    public partial class Estadisticas : System.Web.UI.Page
    {
        DataSet ds;
        string rutaPapelOffice = "Data Source=localhost;Initial Catalog=PapelOffice;Integrated Security=True";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) {
                btn_Buscar_Click(null, null);
            }
        }

        private DataSet Read_DataBase(string Consulta)
        {
            DataSet datset = new DataSet();
            SqlConnection Conexion = new SqlConnection();
            Conexion.ConnectionString = rutaPapelOffice;
            SqlDataAdapter da = new SqlDataAdapter(Consulta, Conexion);
            da.Fill(datset, "tablaAux");
            return datset;
        }

        private void CargarDataGrid(ref DataSet ds)
        {
            Grilla.DataSource = ds.Tables["tablaAux"];
            Grilla.DataBind();
        }

        protected void btn_Buscar_Click(object sender, EventArgs e)
        {
            string opc;
            opc = cmb_Estadisticas.SelectedValue.ToString();
            switch (opc)
            {
                case "0":
                    estadistica1();
                    break;
                case "1":
                    estadistica2();
                    break;
                case "2":
                    estadistica3();
                    break;
                case "3":
                    estadistica4();
                    break;
                case "4":
                    estadistica5();
                    break;
                case "5":
                    estadistica6();
                    break;
            }
        }

        private void estadistica1() 
        {
            string consultaEstadistica = "SELECT TOP 10 Articulos.Id_Articulo,  Articulos.Articulo, SUM(Facturas_Detalle.Cantidad * Facturas_Detalle.PrecioUnitario)as Total"
            + " FROM Facturas_Detalle INNER JOIN Articulos ON Facturas_Detalle.Id_Articulo = Articulos.Id_Articulo"
            + " group by Articulos.Articulo, Articulos.Id_Articulo"
            + " order by Total desc";
            ds = new DataSet();
            ds = Read_DataBase(consultaEstadistica);
            CargarDataGrid(ref ds);
        }
        private void estadistica2()
        {
            string consultaEstadistica = "SELECT TOP 10 Articulos.Id_Articulo, Articulos.Articulo, SUM(Facturas_Detalle.Cantidad)as Cantidad"
            + " FROM Facturas_Detalle INNER JOIN Articulos ON Facturas_Detalle.Id_Articulo = Articulos.Id_Articulo"
            + " group by Articulos.Articulo, Articulos.Id_Articulo"
            + " order by Cantidad desc";
            ds = new DataSet();
            ds = Read_DataBase(consultaEstadistica);
            CargarDataGrid(ref ds);
        }
        private void estadistica3()
        {
            string consultaEstadistica = "SELECT DATEPART(Month,Facturas.Fecha) AS Mes, SUM(Facturas.Total) AS Total"
            + " FROM Facturas GROUP BY DATEPART(Month,Facturas.Fecha) ORDER BY Total DESC";
            ds = new DataSet();
            ds = Read_DataBase(consultaEstadistica);
            CargarDataGrid(ref ds);
        }
        private void estadistica4()
        {
            string consultaEstadistica = "SELECT TOP 10 Facturas.Usr AS [Nombre de Usuario],COUNT(Facturas.Nro_Factura) AS [Cantidad de Ventas]"
            + " FROM Facturas GROUP BY Facturas.Usr ORDER BY [Cantidad de Ventas] DESC";
            ds = new DataSet();
            ds = Read_DataBase(consultaEstadistica);
            CargarDataGrid(ref ds);
        }
        private void estadistica5()
        {
            string consultaEstadistica = "SELECT TOP 10 Proveedores.Nombre + ' ' +"
                + " Proveedores.Apellido AS [Nombre Completo],COUNT(Proveedores_x_Articulos.Id_Articulo) AS Cantidad FROM Proveedores_x_Articulos" 
                + " INNER JOIN Proveedores ON Proveedores_x_Articulos.Id_Proveedor = Proveedores.Id_Proveedor"
                + " GROUP BY Proveedores.Apellido, Proveedores.Nombre ORDER BY Cantidad DESC";
            ds = new DataSet();
            ds = Read_DataBase(consultaEstadistica);
            CargarDataGrid(ref ds);
        }
        private void estadistica6()
        {
            string consultaEstadistica = "SELECT TOP 10 Clientes.Nombre"
                + " + ' ' + Clientes.Apellido AS [Nombre Completo],SUM(Facturas.Total) AS Total FROM Facturas INNER JOIN"
                + " Clientes ON Facturas.Id_Cliente = Clientes.Id_Cliente"
                + " GROUP BY Clientes.Apellido, Clientes.Nombre"
                + " ORDER BY Total DESC";
            ds = new DataSet();
            ds = Read_DataBase(consultaEstadistica);
            CargarDataGrid(ref ds);
        }

        protected void cmb_Estadisticas_SelectedIndexChanged(object sender, EventArgs e)
        {
            btn_Buscar_Click(null, null);
        }
    }
}